﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jzy.Core.Common;
using FortuneHeavenCommon.DataMap;

namespace FortuneHeavenDAL
{
    public class UserInforDAL : BaseDAL
    {
        public UserInfor GetUserInfor(int userid)
        {
            if (userid >= 0)
            { 
                string sql ="Select * from UserInfor where UserID =@p0";
                List<UserInfor> ret= dbOper.Query<UserInfor>(sql, userid);
                if (ret != null && ret.Count > 0)
                {
                    return ret[0];
                }
            }
           
            return null;
        }

        /// <summary>
        /// 获取推广积分最多的
        /// </summary>
        /// <param name="top"></param>
        public List<UserInfor> GetTopSpreadValue(int top)
        {
            string sql = @"Select TOP " + top + @" A.*,B.Accounts as UserName from UserInfor A inner join QPGameUserDB.dbo.AccountsInfo B on A.UserID =B.UserID
         ORDER BY A.SpreadValue DESC";
            List<UserInfor> ret = dbOper.Query<UserInfor>(sql);
            return ret;
        }


        public List<UserInfor> GetSpecialRegisterList(int page, int size, string username,string frompage)
        {
            string sql = "";
            List<UserInfor> list;
            string condition = "";
            if (!string.IsNullOrEmpty(frompage))
            {
                condition = " and A.FromPage = '" + frompage + "'";
            }
            if (username != null && !string.IsNullOrEmpty(username.Trim()))
            {
                sql = @"SELECT FF.*,(ChargeValue - Total - GiveScore) as GameUsedScore  FROM(
Select C.PlayTimeCount,C.OnLineTimeCount, (select Sum(RechargeValue) from QPGameUserDB..RechargeHistory where UserID = A.UserID and RechargeType =1) 
as RechargeValue,A.*,B.Accounts as UserName,B.GameID as GameID,
Isnull((select SUM(Price)*50 from FortuneHeaven..GameShowOrders where [USERID] =B.UserID),0 )AS ChargeValue,
Isnull((select SUm(Score)+SUm(InsureScore) from QPTreasureDB..GameScoreInfo where [USERID] =B.UserID),0) AS Total,
Isnull((select SUM(jf) from QPGameUserDB..cftt_zsjf where fromuser=B.Accounts),0) As GiveScore,
ROW_NUMBER() OVER(ORDER BY A.ID DESC) ROW from UserInfor A 
inner join QPGameUserDB.dbo.AccountsInfo B on A.UserID =B.UserID 
left join QPTreasureDB..GameScoreInfo C On B.UserID = C.UserID
WHERE B.Accounts =@p0 and A.IsSpRegister =1 {0}
) FF
 WHERE FF.ROW BETWEEN  @p1 AND @p2";
                list = dbOper.Query<UserInfor>(string.Format(sql,condition), username, size * (page - 1) + 1, size * page);
            }
            else
            {
                sql = @"SELECT FF.*,(ChargeValue - Total - GiveScore) as GameUsedScore  FROM(
Select C.PlayTimeCount,C.OnLineTimeCount, (select Sum(RechargeValue) from QPGameUserDB..RechargeHistory where UserID = A.UserID and RechargeType =1) 
as RechargeValue,A.*,B.Accounts as UserName,B.GameID as GameID,
Isnull((select SUM(Price)*50 from FortuneHeaven..GameShowOrders where [USERID] =B.UserID),0 )AS ChargeValue,
Isnull((select SUm(Score)+SUm(InsureScore) from QPTreasureDB..GameScoreInfo where [USERID] =B.UserID),0) AS Total,
Isnull((select SUM(jf) from QPGameUserDB..cftt_zsjf where fromuser=B.Accounts),0) As GiveScore,
ROW_NUMBER() OVER(ORDER BY A.ID DESC) ROW from UserInfor A 
inner join QPGameUserDB.dbo.AccountsInfo B on A.UserID =B.UserID 
left join QPTreasureDB..GameScoreInfo C On B.UserID = C.UserID
WHERE A.IsSpRegister =1 {0}
) FF
 WHERE FF.ROW BETWEEN  @p0 AND @p1";
                list = dbOper.Query<UserInfor>(string.Format(sql, condition), size * (page - 1) + 1, size * page);
            }

        
            return list;
        }

        public int GetSpecialRegisterTotalSize(string username, string frompage)
        {
            string condition = "";
            if (!string.IsNullOrEmpty(frompage))
            {
                condition = " and A.FromPage = '" + frompage + "'";
            }
            if (!string.IsNullOrEmpty(username) && username.Trim() != "")
            {
                string sql = @"Select COUNT(A.ID) from UserInfor A 
inner join QPGameUserDB.dbo.AccountsInfo B on A.UserID =B.UserID
WHERE B.Accounts =@p0 and A.IsSpRegister =1 {0}";
                return dbOper.GetScalar<int>(string.Format(sql, condition), username);
            }
            else
            {
                string sql = @"Select COUNT(A.ID) from UserInfor A 
inner join QPGameUserDB.dbo.AccountsInfo B on A.UserID =B.UserID WHERE A.IsSpRegister =1 {0}";
                return dbOper.GetScalar<int>(string.Format(sql, condition));
            }
        }
    }
}
